import  xlrd
# 打开工作簿
book = xlrd.open_workbook(filename=r"D:\Users\北极星的眼泪\Desktop\demo\python基础第三天\5.baidu-员工的人员信息.xls")

# 2.选择其中一个选项卡
st = book.sheet_by_index(0)


pepple_tel = st.col_values(5)[1:] #通过列编号获取电话号码数据
pepple_gender = st.col_values(8)[1:]#通过列编号获取性别数据
pepple_age = st.col_values(7)[1:]#通过列编号获取年龄数据
pepple_salary = st.col_values(11)[1:]#通过列编号获取薪资数据
pepple_company = st.col_values(13)[1:]#通过列编号获取外包公司数据
pepple_address = st.col_values(9)[1:]#通过列编号获取居住地址数据

#移动网络
mobile = ['134','135','136','137','138','139','147','150','151','152','157','158','159','178','182','183','184','187','188','1703','1705','1706']
#联通网络
unicom = ['130','131','132','145','155','156','175','176','185','186','1704','1707','1708','1709','171']
#电信网络
telecommunication = ['133','149','153','173','177','180','181','189','1700','1701','1702']

#统计人数
tel_num = len(pepple_tel)
print("----------------------------------------------------------")
print(f"统计表格中有{tel_num}人")

#手机运营商数量统计
mobile_num = 0
unicom_num = 0
telecommunication_num = 0
for i in pepple_tel:
    qiansan = i[0:3]
    qiansi = i[0:4]
    for yidong in mobile:
        if yidong == qiansan or yidong == qiansi:
            mobile_num += 1
    for liantong in unicom:
        if liantong == qiansan or liantong == qiansi:
            unicom_num +=  1
    for dianxin in telecommunication:
        if dianxin == qiansan or dianxin == qiansi:
            telecommunication_num += 1
mobile_percent = round(mobile_num / tel_num , 4) * 100
unicom_percent = round(unicom_num / tel_num , 4) * 100
telecommunication_percent = round(telecommunication_num / tel_num , 4) * 100
print(f"有{mobile_num}名移动用户，占比{mobile_percent}%")
print(f"有{unicom_num}名联通用户，占比{unicom_percent}%")
print(f"有{telecommunication_num}名电信用户，占比{telecommunication_percent}%")

#性别人数
man = 0
woman = 0

for i in pepple_gender:
    if i == "男":
        man = man +1
    else:
        woman =woman +1
print(f"总公司男性{man}人，女性{woman}人")

#年龄超过45岁
oldman = 0
for i in pepple_age:
    if i > 45.0:
        oldman = oldman +1
print(f"年龄超过45岁的老员工有{oldman}人")

#薪资统计
salary_high = 0
salary_low = 0
for i in pepple_salary:
    if i > 8000.0:
        salary_high = salary_high +1
    elif i < 3000.0:
        salary_low =salary_low +1
print(f"薪资高于8000元的高薪人员有{salary_high}人，薪资低于3000的底薪人员有{salary_low}人")

#传媒人数
media_num = 0
for i in pepple_company:
    if "传媒" in i:
        media_num += 1
print(f"去传媒公司的工作的人员数量有{media_num}名")

#高危地区
dange_num = 0
for i in pepple_address:
    address = i[0:3]
    if "黑龙江" == address or "北京市" == address or "福建省" == address or "四川省" == address:
        dange_num += 1
print(f"可能在疫情高危地区的人数有{dange_num}名")
print("----------------------------------------------------------")
